package common

import (
    "fmt"
    //"database/sql"
    _ "github.com/go-sql-driver/mysql"
    "time"
)

/*
CREATE TABLE IF NOT EXISTS `UserTab`(
   `UserId` INT UNSIGNED AUTO_INCREMENT,
   `Username` VARCHAR(100) NOT NULL,
   `Password` VARCHAR(40) NOT NULL,
   `Email` VARCHAR(100) NOT NULL,
   `Hiredate` DATE, 
   `Regtime` DATETIME,
   PRIMARY KEY (`UserId`),
   UNIQUE KEY `Username` (`Username`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
 */
type MysqlUserTabDesc struct{
    UserId       int
    Username     string
    Password     string
    Email        string
    Hiredate     string   //9999-12-32
    Regtime      string   //9999-12-31 23:59:59
}

/*
 根据用户名查询密码字符串
 */
func (dbw *MysqlDbWorker) MySQL_UserTab_QueryPassword(Username string) (string, error) {
    stmt, _ := dbw.Db.Prepare(`SELECT Password From UserTab where Username = ?`)
    //defer stmt.Close()

    rows, err := stmt.Query(Username)
    //defer rows.Close()
    if err != nil {
        fmt.Printf("MySQL/> %v\n", err)
        return "",err
    }

    var TabDesc MysqlUserTabDesc
    for rows.Next() {
        rows.Scan(&TabDesc.Password)
        if err != nil {
            fmt.Printf("MySQL/> %v\n", err)
            continue
        }
    }

    err = rows.Err()
    if err != nil {
        fmt.Printf("MySQL/> %v\n", err)
        return "",err
    }

    defer rows.Close()
    defer stmt.Close()

    return TabDesc.Password, err
}

/*
 新增一个注册用户信息
 */
func (dbw *MysqlDbWorker) MySQL_UserTab_InsertNew(newdata MysqlUserTabDesc) (int64, int64, error) {
    stmt, err := dbw.Db.Prepare("INSERT INTO UserTab(Username, Password, Email, Hiredate, Regtime) VALUES(?,?,?,?,?);")
    //defer stmt.Close()
    if err != nil{
        fmt.Printf("MySQL/> [Db.Prepare] %v\n", err)
        return -1,0,err
    }

    timestamp:=time.Now().Format("2006-01-02 15:04:05")
    res, err := stmt.Exec(newdata.Username, newdata.Password, newdata.Email, newdata.Hiredate, timestamp)
    if err != nil {
        fmt.Printf("MySQL/> [stmt.Exec] %v\n", err)
        return -2,0,err
    }
    lastId, err := res.LastInsertId()
    if err != nil {
        fmt.Printf("MySQL/> [res.LastInsertId] %v\n", err)
        return -3,0,err
    }
    rowCnt, err := res.RowsAffected()
    if err != nil {
        fmt.Printf("MySQL/> [res.RowsAffected] %v\n", err)
        return -4,0,err
    }
    
    defer stmt.Close()

    return lastId, rowCnt, err
}


/*
 删除一个用户信息
 */
func (dbw *MysqlDbWorker) MySQL_UserTab_DeleteOne(Username string) (int64, error) {
    stmt, err := dbw.Db.Prepare("DELETE FROM UserTab WHERE Username = ?;")
    //defer stmt.Close()
    if err != nil{
        fmt.Printf("MySQL/> [Db.Prepare] %v\n", err)
        return -1,err
    }

    res, err := stmt.Exec(Username)
    if err != nil {
        fmt.Printf("MySQL/> [stmt.Exec] %v\n", err)
        return -2,err
    }
    rowCnt, err := res.RowsAffected()
    if err != nil {
        fmt.Printf("MySQL/> [res.RowsAffected] %v\n", err)
        return -3,err
    }
    
    defer stmt.Close()

    return rowCnt, err
}

/*
 更新一个用户信息
 */
func (dbw *MysqlDbWorker) MySQL_UserTab_Update(Username string, Password string) (int64, error) {
    stmt, err := dbw.Db.Prepare("UPDATE UserTab SET Password = ? WHERE Username = ?;")
    //defer stmt.Close()
    if err != nil{
        fmt.Printf("MySQL/> [Db.Prepare] %v\n", err)
        return -1,err
    }

    res, err := stmt.Exec(Password, Username)
    if err != nil {
        fmt.Printf("MySQL/> [stmt.Exec] %v\n", err)
        return -2,err
    }
    rowCnt, err := res.RowsAffected()
    if err != nil {
        fmt.Printf("MySQL/> [res.RowsAffected] %v\n", err)
        return -3,err
    }
    
    defer stmt.Close()

    return rowCnt, err
}

